﻿using System;
using System.Data;
using System.Data.Common;
using System.Configuration;
using System.Collections.Generic;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Shuba.Model;

namespace Shuba.Data
{
    public class PipelineBulk
    {
        public PipelineBulk() 
        { 
        }

        public int GetRowNumberPipelineApproval(string update_by, string batch_id)
        {
            int jmlRow = 0;
            Database db = DBConnection.CreateDatabase();
            string sql;
            DbCommand cmd;
            sql = @"SELECT count(*) AS jmlData FROM ( 
                    SELECT * FROM TBL_PIPELINE_TMP WHERE RTRIM(UPDATE_BY) <> '' + @UPDATE_BY + '' AND [BATCH_ID] = @BATCH_ID AND STATUS = 'P' AND ERR_LINE = 0)AS DATA";
            cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, update_by);
            db.AddInParameter(cmd, "@BATCH_ID", DbType.String, batch_id);
            cmd.CommandTimeout = 1800;
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                    jmlRow = (int)dr["jmlData"];
                dr.Close();
            }
            return jmlRow;
        }

        public int GetRowNumberPipelineByValues(string value, string batch_id)
        {
            int jmlRow = 0;
            Database db = DBConnection.CreateDatabase();
            string sql;
            DbCommand cmd;
            sql = @"SELECT count(*) AS jmlData FROM( 
                    SELECT * FROM TBL_PIPELINE_TMP (NOLOCK) WHERE [BATCH_ID] = @BATCH_ID AND IS_DELETED = '0' AND STATUS = 'R' AND ((UNIT_CODE LIKE '%' + @PARAM + '%') OR (FULL_NAME LIKE '%' + @PARAM + '%'))) AS DATA";

            cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@PARAM", DbType.String, value);
            db.AddInParameter(cmd, "@BATCH_ID", DbType.String, batch_id);
            cmd.CommandTimeout = 1800;
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                    jmlRow = (int)dr["jmlData"];
                dr.Close();
            }
            return jmlRow;
        }

        public int GetRowNumberPipelineReal(string value, string batch_id)
        {
            int jmlRow = 0;
            Database db = DBConnection.CreateDatabase();
            string sql;
            DbCommand cmd;
            /*
            sql = @"SELECT count(*) AS jmlData FROM( 
                    SELECT * FROM TBL_PIPELINE WHERE [BATCH_ID] = @BATCH_ID AND ((UNIT_CODE LIKE '%' + @PARAM + '%') OR (FULL_NAME LIKE '%' + @PARAM + '%'))) AS DATA";
            */
            sql = @"SELECT count(0) AS jmlData FROM ( 
                    SELECT PT.UNIT_CODE FROM [TBL_PIPELINE] (NOLOCK) P RIGHT OUTER JOIN TBL_PIPELINE_TMP (NOLOCK) PT
                    ON P.UNIT_CODE = PT.UNIT_CODE 
	                    AND P.FULL_NAME = PT.FULL_NAME
	                    AND P.ADDRESS_BUSINESS = PT.ADDRESS_BUSINESS
                    WHERE PT.ERR_LINE = 0 AND PT.STATUS = 'A' AND PT.[BATCH_ID] = @BATCH_ID AND ((PT.UNIT_CODE LIKE '%' + @PARAM + '%') OR (PT.FULL_NAME LIKE '%' + @PARAM + '%'))) AS DATA";

            cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@PARAM", DbType.String, value);
            db.AddInParameter(cmd, "@BATCH_ID", DbType.String, batch_id);
            cmd.CommandTimeout = 1800;
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                    jmlRow = (int)dr["jmlData"];
                dr.Close();
            }
            return jmlRow;
        }

        public int GetRowNumberPipelineFailed(string value, string batch_id)
        {
            int jmlRow = 0;
            Database db = DBConnection.CreateDatabase();
            string sql;
            DbCommand cmd;
            sql = @"SELECT count(1) AS jmlData FROM( 
                    SELECT * FROM TBL_PIPELINE_TMP WHERE [BATCH_ID] = @BATCH_ID AND IS_DELETED = '0' AND ERR_LINE > 0 AND ((UNIT_CODE LIKE '%' + @PARAM + '%') OR (FULL_NAME LIKE '%' + @PARAM + '%'))) AS DATA";

            cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@PARAM", DbType.String, value);
            db.AddInParameter(cmd, "@BATCH_ID", DbType.String, batch_id);
            cmd.CommandTimeout = 1800;
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                    jmlRow = (int)dr["jmlData"];
                dr.Close();
            }
            return jmlRow;
        }

        public List<PipelineBulkInfo> GetListPipeline(string value, string batch_id, int currPage, int dataPerPage, string sortBy, string sortDir)
        {
            int barisAwal = 1 + (currPage - 1) * dataPerPage;
            int barisAkhir = barisAwal + dataPerPage - 1;
            List<PipelineBulkInfo> list = new List<PipelineBulkInfo>();
            Database db = DBConnection.CreateDatabase();
            string sql;
            DbCommand cmd;
            sql = "SELECT  * FROM ( " +
                  "SELECT ROW_NUMBER() OVER (ORDER BY " + sortBy + " " + sortDir + ") AS Row, " +
                  "* FROM TBL_PIPELINE_TMP (NOLOCK) WHERE [BATCH_ID] = @BATCH_ID AND IS_DELETED = '0' AND STATUS = 'R' AND ((UNIT_CODE LIKE '%' + @PARAM + '%') OR (FULL_NAME LIKE '%' + @PARAM + '%'))" +
                  ") AS LogWithRowNumbers " +
                  "WHERE  Row >= @BARIS_AWAL AND Row <= @BARIS_AKHIR";
            cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@PARAM", DbType.String, value);
            db.AddInParameter(cmd, "@BATCH_ID", DbType.String, batch_id);
            db.AddInParameter(cmd, "@BARIS_AWAL", DbType.Int32, barisAwal);
            db.AddInParameter(cmd, "@BARIS_AKHIR", DbType.Int32, barisAkhir);
            cmd.CommandTimeout = 3600;

            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new PipelineBulkInfo(dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetDouble(4), dr.GetString(5), dr.GetString(6), dr.GetString(7), dr.GetString(8), dr.GetString(9), dr.GetString(10), dr.GetString(11), dr.GetString(12), dr.GetDateTime(13), dr.GetString(14), dr.GetString(15), dr.GetString(16), dr.GetDouble(17), dr.GetDouble(18), dr.GetDouble(19), dr.GetDouble(20), dr.GetDouble(21), dr.GetDouble(22), dr.GetDouble(23), dr.GetDouble(24), dr.GetDouble(25), dr.GetDouble(26), dr.GetDouble(27), dr.GetDouble(28), dr.GetDouble(29), dr.GetString(30), dr.GetDateTime(31), dr.GetString(32), dr.GetDateTime(33), dr.GetString(34), dr.GetString(35), dr.GetDateTime(36), dr.GetString(37), dr.GetString(38), dr.GetString(39), dr.GetString(40), dr.GetInt32(41), dr.GetString(42), dr.GetString(43), dr.GetString(44), "", ""));
                }
                dr.Close();
            }
            return list;
        }

        public List<PipelineBulkInfo> GetListPipelineReal(string value, string batch_id, int currPage, int dataPerPage, string sortBy, string sortDir)
        {
            int barisAwal = 1 + (currPage - 1) * dataPerPage;
            int barisAkhir = barisAwal + dataPerPage - 1;
            List<PipelineBulkInfo> list = new List<PipelineBulkInfo>();
            Database db = DBConnection.CreateDatabase();
            string table = "PT.";
            if (sortBy == "DOWNLOAD_DATE" || sortBy == "ASSIGNED_TO" || sortBy == "IS_ASSIGNED") table = "P.";
            string sql;

            DbCommand cmd;
            sql = "SELECT  * FROM ( " +
                  " SELECT ROW_NUMBER() OVER (ORDER BY " + table + sortBy + " " + sortDir + ") AS Row, " +
                  " ISNULL(CAST(P.[ROW_ID] AS VARCHAR(40)), '') AS ROW_ID,PT.[UNIT_CODE],PT.[FULL_NAME],PT.[JENIS_USAHA] " +
                     " ,PT.[RENCANA_PLAFON],PT.[MOBILE_PHONE],PT.[OFFICE_PHONE] " +
                     " ,PT.[RESIDENTIAL_PHONE],PT.[KODYA_KAB_BUSINESS],PT.[KECAMATAN_BUSINESS] " +
                     " ,PT.[KELURAHAN_BUSINESS],PT.[ADDRESS_BUSINESS],PT.[ZIP_CODE_BUSINESS] " +
                     " ,PT.[DATE_VISIT_PLAN],PT.[PROSPECT_TYPE],PT.[DEBTOR_TYPE] " +
                     " ,PT.[CHANNELING],PT.[OMZET_1],PT.[OMZET_2] " +
                     " ,PT.[OMZET_3],PT.[OMZET_4],PT.[OMZET_5] " +
                     " ,PT.[OMZET_6],PT.[OMZET_7],PT.[OMZET_8] " +
                     " ,PT.[OMZET_9],PT.[OMZET_10],PT.[OMZET_11] " +
                     " ,PT.[OMZET_12],PT.[OMZET_AVERAGE],PT.[ENTRY_BY] " +
                     " ,PT.[ENTRY_DATE],PT.[UPDATE_BY],PT.[UPDATE_DATE] " +
                     " ,ISNULL(P.[IS_DOWNLOADED], '') IS_DOWNLOADED, ISNULL(P.[DOWNLOAD_BY], '') DOWNLOAD_BY, ISNULL(P.[DOWNLOAD_DATE], '01-01-2009') DOWNLOAD_DATE " +
                     " ,PT.[EKSTERNAL],PT.[REFERENSI],ISNULL(P.[IS_ASSIGNED], '') IS_ASSIGNED " +
                     " ,ISNULL(P.[ASSIGNED_TO], '') ASSIGNED_TO, ISNULL(P.[ASSIGNED_DATE], '01-01-2009') ASSIGNED_DATE,PT.[BATCH_ID],PT.[FLAG_FREEZE],PT.[ACT_TYPE] " +
                  " FROM [TBL_PIPELINE] (NOLOCK) P RIGHT OUTER JOIN TBL_PIPELINE_TMP (NOLOCK) PT " +
                " ON P.UNIT_CODE = PT.UNIT_CODE  " +
	                " AND P.FULL_NAME = PT.FULL_NAME " +
	                " AND P.ADDRESS_BUSINESS = PT.ADDRESS_BUSINESS " +
                " WHERE PT.ERR_LINE = 0 AND PT.STATUS = 'A' AND PT.[BATCH_ID] = @BATCH_ID AND ((PT.UNIT_CODE LIKE '%' + @PARAM + '%') OR (PT.FULL_NAME LIKE '%' + @PARAM + '%')) " +
                  ") AS LogWithRowNumbers " +
                  "WHERE  Row >= @BARIS_AWAL AND Row <= @BARIS_AKHIR";

            cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@PARAM", DbType.String, value);
            db.AddInParameter(cmd, "@BATCH_ID", DbType.String, batch_id);
            db.AddInParameter(cmd, "@BARIS_AWAL", DbType.Int32, barisAwal);
            db.AddInParameter(cmd, "@BARIS_AKHIR", DbType.Int32, barisAkhir);
            cmd.CommandTimeout = 3600;
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new PipelineBulkInfo(dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetDouble(5), dr.GetString(6), dr.GetString(7), dr.GetString(8), dr.GetString(9), dr.GetString(10), dr.GetString(11), dr.GetString(12), dr.GetString(13), dr.GetDateTime(14), dr.GetString(15), dr.GetString(16), dr.GetString(17), dr.GetDouble(18), dr.GetDouble(19), dr.GetDouble(20), dr.GetDouble(21), dr.GetDouble(22), dr.GetDouble(23), dr.GetDouble(24), dr.GetDouble(25), dr.GetDouble(26), dr.GetDouble(27), dr.GetDouble(28), dr.GetDouble(29), dr.GetDouble(30), dr.GetString(31), dr.GetDateTime(32), dr.GetString(33), dr.GetDateTime(34), dr.GetString(35), dr.GetString(36), dr.GetDateTime(37), dr.GetString(38), dr.GetString(39), dr.GetString(40), dr.GetString(41), dr.GetDateTime(42), dr.GetString(43), dr.GetInt16(44), dr.GetString(45)));
                }
                dr.Close();
            }
            return list;
        }

        public List<PipelineBulkInfo> GetListApprovalPipeline(string update_by, string batch_id, int currPage, int dataPerPage, string sortBy, string sortDir)
        {
            int barisAwal = 1 + (currPage - 1) * dataPerPage;
            int barisAkhir = barisAwal + dataPerPage - 1;
            List<PipelineBulkInfo> list = new List<PipelineBulkInfo>();
            Database db = DBConnection.CreateDatabase();
            string sql;
            DbCommand cmd;
            sql = "SELECT  * FROM ( " +
                        "SELECT ROW_NUMBER() OVER (ORDER BY " + sortBy + " " + sortDir + ") AS Row, " +
                        "UNIT_CODE,FULL_NAME,JENIS_USAHA,RENCANA_PLAFON " +
                           ",MOBILE_PHONE,OFFICE_PHONE,RESIDENTIAL_PHONE,KODYA_KAB_BUSINESS,KECAMATAN_BUSINESS " +
                           ",KELURAHAN_BUSINESS,ADDRESS_BUSINESS,ZIP_CODE_BUSINESS,DATE_VISIT_PLAN,PROSPECT_TYPE " +
                           ",DEBTOR_TYPE,CHANNELING,OMZET_1,OMZET_2,OMZET_3,OMZET_4,OMZET_5,OMZET_6,OMZET_7,OMZET_8 " +
                           ",OMZET_9,OMZET_10,OMZET_11,OMZET_12,OMZET_AVERAGE,ENTRY_BY,ENTRY_DATE,UPDATE_BY " +
                           ",UPDATE_DATE,IS_DOWNLOADED,DOWNLOAD_BY,DOWNLOAD_DATE,ACT_TYPE,EKSTERNAL,REFERENSI,BATCH_ID " +
                           ",ERR_LINE,ERR_DESC,STATUS,IS_DELETED " +
                        "FROM TBL_PIPELINE_TMP WHERE RTRIM(UPDATE_BY) <> '' + @UPDATE_BY + '' AND [BATCH_ID] = @BATCH_ID AND STATUS = 'P' AND ERR_LINE = 0" +
                   ")AS LogWithRowNumbers " +
                   "WHERE  Row >= @BARIS_AWAL AND Row <= @BARIS_AKHIR";
            cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, update_by);
            db.AddInParameter(cmd, "@BATCH_ID", DbType.String, batch_id);
            db.AddInParameter(cmd, "@BARIS_AWAL", DbType.Int32, barisAwal);
            db.AddInParameter(cmd, "@BARIS_AKHIR", DbType.Int32, barisAkhir);
            cmd.CommandTimeout = 3600;
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new PipelineBulkInfo(dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetDouble(4), dr.GetString(5), dr.GetString(6), dr.GetString(7), dr.GetString(8), dr.GetString(9), dr.GetString(10), dr.GetString(11), dr.GetString(12), dr.GetDateTime(13), dr.GetString(14), dr.GetString(15), dr.GetString(16), dr.GetDouble(17), dr.GetDouble(18), dr.GetDouble(19), dr.GetDouble(20), dr.GetDouble(21), dr.GetDouble(22), dr.GetDouble(23), dr.GetDouble(24), dr.GetDouble(25), dr.GetDouble(26), dr.GetDouble(27), dr.GetDouble(28), dr.GetDouble(29), dr.GetString(30), dr.GetDateTime(31), dr.GetString(32), dr.GetDateTime(33), dr.GetString(34), dr.GetString(35), dr.GetDateTime(36), dr.GetString(37), dr.GetString(38), dr.GetString(39), dr.GetString(40), dr.GetInt32(41), dr.GetString(42), dr.GetString(43), dr.GetString(44)));
                }
                dr.Close();
            }
            return list;
        }

        public List<PipelineBulkInfo> GetListPipelineFailed(string value, string batch_id, int currPage, int dataPerPage)
        {
            int barisAwal = 1 + (currPage - 1) * dataPerPage;
            int barisAkhir = barisAwal + dataPerPage - 1;
            List<PipelineBulkInfo> list = new List<PipelineBulkInfo>();
            Database db = DBConnection.CreateDatabase();

            string sql;
            try
            {
                DbCommand cmd;
                sql = @"SELECT  * FROM ( 
                        SELECT ROW_NUMBER() OVER (ORDER BY UPDATE_DATE DESC) AS Row, 
                        UNIT_CODE,FULL_NAME,JENIS_USAHA,RENCANA_PLAFON 
                           ,MOBILE_PHONE,OFFICE_PHONE,RESIDENTIAL_PHONE,KODYA_KAB_BUSINESS,KECAMATAN_BUSINESS 
                           ,KELURAHAN_BUSINESS,ADDRESS_BUSINESS,ZIP_CODE_BUSINESS,DATE_VISIT_PLAN,PROSPECT_TYPE 
                           ,DEBTOR_TYPE,CHANNELING,OMZET_1,OMZET_2,OMZET_3,OMZET_4,OMZET_5,OMZET_6,OMZET_7,OMZET_8 
                           ,OMZET_9,OMZET_10,OMZET_11,OMZET_12,OMZET_AVERAGE,ENTRY_BY,ENTRY_DATE,UPDATE_BY 
                           ,UPDATE_DATE,IS_DOWNLOADED,DOWNLOAD_BY,DOWNLOAD_DATE,ACT_TYPE,EKSTERNAL,REFERENSI,BATCH_ID 
                           ,ERR_LINE,ERR_DESC,STATUS,IS_DELETED 
                        FROM TBL_PIPELINE_TMP WHERE [BATCH_ID] = @BATCH_ID AND IS_DELETED = '0' AND ERR_LINE > 0 AND ((UNIT_CODE LIKE @PARAM) OR (FULL_NAME LIKE @PARAM))
                        )AS LogWithRowNumbers 
                   WHERE  Row >= @BARIS_AWAL AND Row <= @BARIS_AKHIR";
                cmd = db.GetSqlStringCommand(sql);
                db.AddInParameter(cmd, "@PARAM", DbType.String, "%" + value + "%");
                db.AddInParameter(cmd, "@BATCH_ID", DbType.String, batch_id);
                db.AddInParameter(cmd, "@BARIS_AWAL", DbType.Int32, barisAwal);
                db.AddInParameter(cmd, "@BARIS_AKHIR", DbType.Int32, barisAkhir);
                cmd.CommandTimeout = 1800;
                using (IDataReader dr = db.ExecuteReader(cmd))
                {
                    while (dr.Read())
                    {
                        list.Add(new PipelineBulkInfo(dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetDouble(4), dr.GetString(5), dr.GetString(6), dr.GetString(7), dr.GetString(8), dr.GetString(9), dr.GetString(10), dr.GetString(11), dr.GetString(12), dr.GetDateTime(13), dr.GetString(14), dr.GetString(15), dr.GetString(16), dr.GetDouble(17), dr.GetDouble(18), dr.GetDouble(19), dr.GetDouble(20), dr.GetDouble(21), dr.GetDouble(22), dr.GetDouble(23), dr.GetDouble(24), dr.GetDouble(25), dr.GetDouble(26), dr.GetDouble(27), dr.GetDouble(28), dr.GetDouble(29), dr.GetString(30), dr.GetDateTime(31), dr.GetString(32), dr.GetDateTime(33), dr.GetString(34), dr.GetString(35), dr.GetDateTime(36), dr.GetString(37), dr.GetString(38), dr.GetString(39), dr.GetString(40), dr.GetInt32(41), dr.GetString(42), dr.GetString(43), dr.GetString(44)));
                    }
                    dr.Close();
                }
            }
            catch (Exception ex) { }
            return list;
        }

        public List<PipelineBulkInfo> GetListPipelineFailed(string value, string batch_id)
        {
            List<PipelineBulkInfo> list = new List<PipelineBulkInfo>();
            Database db = DBConnection.CreateDatabase();
            string sql;
            DbCommand cmd;
            sql = @"SELECT UNIT_CODE,FULL_NAME,JENIS_USAHA,RENCANA_PLAFON
                          ,MOBILE_PHONE,OFFICE_PHONE,RESIDENTIAL_PHONE,KODYA_KAB_BUSINESS,KECAMATAN_BUSINESS
                          ,KELURAHAN_BUSINESS,ADDRESS_BUSINESS,ZIP_CODE_BUSINESS,DATE_VISIT_PLAN,PROSPECT_TYPE
                          ,DEBTOR_TYPE,CHANNELING,OMZET_1,OMZET_2,OMZET_3,OMZET_4,OMZET_5,OMZET_6,OMZET_7,OMZET_8
                          ,OMZET_9,OMZET_10,OMZET_11,OMZET_12,OMZET_AVERAGE,ENTRY_BY,ENTRY_DATE,UPDATE_BY
                          ,UPDATE_DATE,IS_DOWNLOADED,DOWNLOAD_BY,DOWNLOAD_DATE,ACT_TYPE,EKSTERNAL,REFERENSI,BATCH_ID
                          ,ERR_LINE,ERR_DESC,STATUS,IS_DELETED
                        FROM TBL_PIPELINE_TMP WHERE [BATCH_ID] = @BATCH_ID AND IS_DELETED = '0' AND ERR_LINE > 0 AND ((UNIT_CODE LIKE '%' + @PARAM + '%') OR (FULL_NAME LIKE '%' + @PARAM + '%'))";
            cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@PARAM", DbType.String, value);
            db.AddInParameter(cmd, "@BATCH_ID", DbType.String, batch_id);
            cmd.CommandTimeout = 1800;
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new PipelineBulkInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetDouble(3), dr.GetString(4), dr.GetString(5), dr.GetString(6), dr.GetString(7), dr.GetString(8), dr.GetString(9), dr.GetString(10), dr.GetString(11), dr.GetDateTime(12), dr.GetString(13), dr.GetString(14), dr.GetString(15), dr.GetDouble(16), dr.GetDouble(17), dr.GetDouble(18), dr.GetDouble(19), dr.GetDouble(20), dr.GetDouble(21), dr.GetDouble(22), dr.GetDouble(23), dr.GetDouble(24), dr.GetDouble(25), dr.GetDouble(26), dr.GetDouble(27), dr.GetDouble(28), dr.GetString(29), dr.GetDateTime(30), dr.GetString(31), dr.GetDateTime(32), dr.GetString(33), dr.GetString(34), dr.GetDateTime(35), dr.GetString(36), dr.GetString(37), dr.GetString(38), dr.GetString(39), dr.GetInt32(40), dr.GetString(41), dr.GetString(42), dr.GetString(43)));
                }
                dr.Close();
            }
            return list;
        }

        public PipelineBulkInfo GetApprovalByID(string UNIT_CODE, string FULL_NAME, string ADDRESS_BUSINESS)
        {
            PipelineBulkInfo ci = new PipelineBulkInfo();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"SELECT * FROM TBL_PIPELINE_TMP WHERE LTRIM(RTRIM(UNIT_CODE)) = @UNIT_CODE
                                AND LTRIM(RTRIM(FULL_NAME)) = @FULL_NAME
                                AND LTRIM(RTRIM(ADDRESS_BUSINESS)) = @ADDRESS_BUSINESS
                                ");
            db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, UNIT_CODE.Trim());
            db.AddInParameter(cmd, "@FULL_NAME", DbType.String, FULL_NAME.Trim());
            db.AddInParameter(cmd, "@ADDRESS_BUSINESS", DbType.String, ADDRESS_BUSINESS.Trim());
            cmd.CommandTimeout = 1800;
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    ci = new PipelineBulkInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetDouble(3), dr.GetString(4), dr.GetString(5), dr.GetString(6), dr.GetString(7), dr.GetString(8), dr.GetString(9), dr.GetString(10), dr.GetString(11), dr.GetDateTime(12), dr.GetString(13), dr.GetString(14), dr.GetString(15), dr.GetDouble(16), dr.GetDouble(17), dr.GetDouble(18), dr.GetDouble(19), dr.GetDouble(20), dr.GetDouble(21), dr.GetDouble(22), dr.GetDouble(23), dr.GetDouble(24), dr.GetDouble(25), dr.GetDouble(26), dr.GetDouble(27), dr.GetDouble(28), dr.GetString(29), dr.GetDateTime(30), dr.GetString(31), dr.GetDateTime(32), dr.GetString(33), dr.GetString(34), dr.GetDateTime(35), dr.GetString(36), dr.GetString(37), dr.GetString(38), dr.GetString(39), dr.GetInt32(40), dr.GetString(41), dr.GetString(42), dr.GetString(43));
                }
                dr.Close();
            }
            return ci;
        }

        public PipelineBulkInfo GetApprovalByIDandBatch(string UNIT_CODE, string FULL_NAME, string ADDRESS_BUSINESS,string BATCH_ID)
        {
            PipelineBulkInfo ci = new PipelineBulkInfo();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"SELECT * FROM TBL_PIPELINE_TMP WHERE LTRIM(RTRIM(UNIT_CODE)) = @UNIT_CODE
                                AND LTRIM(RTRIM(FULL_NAME)) = @FULL_NAME
                                AND LTRIM(RTRIM(ADDRESS_BUSINESS)) = @ADDRESS_BUSINESS
                                AND LTRIM(RTRIM(BATCH_ID)) = @BATCH_ID
                                ");
            db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, UNIT_CODE.Trim());
            db.AddInParameter(cmd, "@FULL_NAME", DbType.String, FULL_NAME.Trim());
            db.AddInParameter(cmd, "@ADDRESS_BUSINESS", DbType.String, ADDRESS_BUSINESS.Trim());
            db.AddInParameter(cmd, "@BATCH_ID", DbType.String, BATCH_ID.Trim());
            cmd.CommandTimeout = 1800;
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    ci = new PipelineBulkInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetDouble(3), dr.GetString(4), dr.GetString(5), dr.GetString(6), dr.GetString(7), dr.GetString(8), dr.GetString(9), dr.GetString(10), dr.GetString(11), dr.GetDateTime(12), dr.GetString(13), dr.GetString(14), dr.GetString(15), dr.GetDouble(16), dr.GetDouble(17), dr.GetDouble(18), dr.GetDouble(19), dr.GetDouble(20), dr.GetDouble(21), dr.GetDouble(22), dr.GetDouble(23), dr.GetDouble(24), dr.GetDouble(25), dr.GetDouble(26), dr.GetDouble(27), dr.GetDouble(28), dr.GetString(29), dr.GetDateTime(30), dr.GetString(31), dr.GetDateTime(32), dr.GetString(33), dr.GetString(34), dr.GetDateTime(35), dr.GetString(36), dr.GetString(37), dr.GetString(38), dr.GetString(39), dr.GetInt32(40), dr.GetString(41), dr.GetString(42), dr.GetString(43));
                }
                dr.Close();
            }
            return ci;
        }

        public int ApproveBulk(string UNIT_CODE, string FULL_NAME, string ADDRESS_BUSINESS,string BATCH_ID, string ACT_TYPE)
        {
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetStoredProcCommand("PIPELINE_APPROVAL_BULK");

            db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, UNIT_CODE.Trim());
            db.AddInParameter(cmd, "@FULL_NAME", DbType.String, FULL_NAME.Trim());
            db.AddInParameter(cmd, "@ADDRESS_BUSINESS", DbType.String, ADDRESS_BUSINESS.Trim());
            db.AddInParameter(cmd, "@ACT_TYPE", DbType.String, ACT_TYPE.Trim());
            db.AddInParameter(cmd, "@BATCH_ID", DbType.String, BATCH_ID.Trim());
            cmd.CommandTimeout = 1800;

            return db.ExecuteNonQuery(cmd);
        }

        public Int32[] ApproveBulkAll(string batch_id, string status)
        {
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetStoredProcCommand("PIPELINE_APPROVAL_ALL");

            db.AddInParameter(cmd, "@BATCH_ID", DbType.String, batch_id);
            db.AddInParameter(cmd, "@STATUS", DbType.String, status);
            db.AddOutParameter(cmd, "@ROWCOUNT_I", DbType.Int32, 50);
            db.AddOutParameter(cmd, "@ROWCOUNT_U", DbType.Int32, 50);
            db.AddOutParameter(cmd, "@ROWCOUNT_D", DbType.Int32, 50);
            cmd.CommandTimeout = 5600;
            db.ExecuteNonQuery(cmd);
            int insertCount = Convert.ToInt32(db.GetParameterValue(cmd, "@ROWCOUNT_I"));
            int updateCount = Convert.ToInt32(db.GetParameterValue(cmd, "@ROWCOUNT_U"));
            int deleteCount = Convert.ToInt32(db.GetParameterValue(cmd, "@ROWCOUNT_D"));
            Int32[] countArr = new Int32[3] { insertCount, updateCount, deleteCount };
            return countArr;
        }

        public int Reject(string UNIT_CODE, string FULL_NAME, string ADDRESS_BUSINESS, string BATCH_ID)
        {
            Database db = DBConnection.CreateDatabase();
            //UPDATE TBL_PIPELINE_TMP SET STATUS ='R'

            DbCommand cmd = db.GetSqlStringCommand(@"
                            UPDATE TBL_PIPELINE_TMP SET STATUS = 'R', IS_DELETED = '0'
                            WHERE (LTRIM(RTRIM(UNIT_CODE)) = @UNIT_CODE) 
                                  AND (LTRIM(RTRIM(FULL_NAME)) = @FULL_NAME)
                                  AND (LTRIM(RTRIM(ADDRESS_BUSINESS)) = @ADDRESS_BUSINESS)
                                  AND (LTRIM(RTRIM(BATCH_ID)) = @BATCH_ID)");
            db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, UNIT_CODE.Trim());
            db.AddInParameter(cmd, "@FULL_NAME", DbType.String, FULL_NAME.Trim());
            db.AddInParameter(cmd, "@ADDRESS_BUSINESS", DbType.String, ADDRESS_BUSINESS.Trim());
            db.AddInParameter(cmd, "@BATCH_ID", DbType.String, BATCH_ID.Trim());
            cmd.CommandTimeout = 1800;
            return db.ExecuteNonQuery(cmd);
        }

        public void delete(string batch_id)
        {
            Database db = DBConnection.CreateDatabase();

            DbCommand cmd = db.GetSqlStringCommand("UPDATE TBL_TOPUP_TMP SET IS_DELETED = '1' FROM TBL_PIPELINE_TMP WHERE BATCH_ID = @BATCH_ID");
            db.AddInParameter(cmd, "@BATCH_ID", DbType.String, batch_id);
            cmd.CommandTimeout = 1800;
            db.ExecuteNonQuery(cmd);
        }

        private bool CharToBool(string source)
        {
            if (source == "0")
            {
                return false;
            }
            else
            {
                return true;
            }
        }

        private string BoolToChar(bool source)
        {
            if (source)
            {
                return "1";
            }
            else
            {
                return "0";
            }
        }

        public PipelineBulkInfo GetAll(string ROW_ID)
        {
            PipelineBulkInfo ci = new PipelineBulkInfo();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"SELECT UNIT_CODE,FULL_NAME,JENIS_USAHA,RENCANA_PLAFON
                           ,MOBILE_PHONE,OFFICE_PHONE,RESIDENTIAL_PHONE,KODYA_KAB_BUSINESS,KECAMATAN_BUSINESS
                           ,KELURAHAN_BUSINESS,ADDRESS_BUSINESS,ZIP_CODE_BUSINESS,DATE_VISIT_PLAN,PROSPECT_TYPE
                           ,DEBTOR_TYPE,CHANNELING,OMZET_1,OMZET_2,OMZET_3,OMZET_4,OMZET_5,OMZET_6,OMZET_7,OMZET_8
                           ,OMZET_9,OMZET_10,OMZET_11,OMZET_12,OMZET_AVERAGE,ENTRY_BY,ENTRY_DATE,UPDATE_BY
                           ,UPDATE_DATE,IS_DOWNLOADED,DOWNLOAD_BY,DOWNLOAD_DATE,ACT_TYPE,EKSTERNAL,REFERENSI,BATCH_ID
                           ,ERR_LINE,ERR_DESC,STATUS,IS_DELETED
                                FROM TBL_PIPELINE_TMP WHERE LTRIM(RTRIM(ROW_ID)) LIKE '' + @ROW_ID + ''");
            db.AddInParameter(cmd, "@ROW_ID", DbType.String, ROW_ID.Trim());
            cmd.CommandTimeout = 1800;
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    ci = new PipelineBulkInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetDouble(3), dr.GetString(4), dr.GetString(5), dr.GetString(6), dr.GetString(7), dr.GetString(8), dr.GetString(9), dr.GetString(10), dr.GetString(11), dr.GetDateTime(12), dr.GetString(13), dr.GetString(14), dr.GetString(15), dr.GetDouble(16), dr.GetDouble(17), dr.GetDouble(18), dr.GetDouble(19), dr.GetDouble(20), dr.GetDouble(21), dr.GetDouble(22), dr.GetDouble(23), dr.GetDouble(24), dr.GetDouble(25), dr.GetDouble(26), dr.GetDouble(27), dr.GetDouble(28), dr.GetString(29), dr.GetDateTime(30), dr.GetString(31), dr.GetDateTime(32), dr.GetString(33), dr.GetString(34), dr.GetDateTime(35), dr.GetString(36), dr.GetString(37), dr.GetString(38), dr.GetString(39), dr.GetInt32(40), dr.GetString(41), dr.GetString(42), dr.GetString(43));
                }
                dr.Close();
            }
            return ci;
        }

        public PipelineBulkInfo Get(string UNIT_CODE,string FULL_NAME,string ADDRESS_BUSINESS)
        {
            PipelineBulkInfo ci = new PipelineBulkInfo();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"SELECT UNIT_CODE,FULL_NAME,JENIS_USAHA,RENCANA_PLAFON
                           ,MOBILE_PHONE,OFFICE_PHONE,RESIDENTIAL_PHONE,KODYA_KAB_BUSINESS,KECAMATAN_BUSINESS
                           ,KELURAHAN_BUSINESS,ADDRESS_BUSINESS,ZIP_CODE_BUSINESS,DATE_VISIT_PLAN,PROSPECT_TYPE
                           ,DEBTOR_TYPE,CHANNELING,OMZET_1,OMZET_2,OMZET_3,OMZET_4,OMZET_5,OMZET_6,OMZET_7,OMZET_8
                           ,OMZET_9,OMZET_10,OMZET_11,OMZET_12,OMZET_AVERAGE,ENTRY_BY,ENTRY_DATE,UPDATE_BY
                           ,UPDATE_DATE,IS_DOWNLOADED,DOWNLOAD_BY,DOWNLOAD_DATE,ACT_TYPE,EKSTERNAL,REFERENSI,BATCH_ID
                           ,ERR_LINE,ERR_DESC,STATUS,IS_DELETED
                                FROM TBL_PIPELINE_TMP WHERE LTRIM(RTRIM(UNIT_CODE)) = @UNIT_CODE AND FULL_NAME = @FULL_NAME
                                AND ADDRESS_BUSINESS = @ADDRESS_BUSINESS");
            db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, UNIT_CODE.Trim());
            db.AddInParameter(cmd, "@FULL_NAME", DbType.String, FULL_NAME.Trim());
            db.AddInParameter(cmd, "@ADDRESS_BUSINESS", DbType.String, ADDRESS_BUSINESS.Trim());
            cmd.CommandTimeout = 1800;
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    ci = new PipelineBulkInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetDouble(3), dr.GetString(4), dr.GetString(5), dr.GetString(6), dr.GetString(7), dr.GetString(8), dr.GetString(9), dr.GetString(10), dr.GetString(11), dr.GetDateTime(12), dr.GetString(13), dr.GetString(14), dr.GetString(15), dr.GetDouble(16), dr.GetDouble(17), dr.GetDouble(18), dr.GetDouble(19), dr.GetDouble(20), dr.GetDouble(21), dr.GetDouble(22), dr.GetDouble(23), dr.GetDouble(24), dr.GetDouble(25), dr.GetDouble(26), dr.GetDouble(27), dr.GetDouble(28), dr.GetString(29), dr.GetDateTime(30), dr.GetString(31), dr.GetDateTime(32), dr.GetString(33), dr.GetString(34), dr.GetDateTime(35), dr.GetString(36), dr.GetString(37), dr.GetString(38), dr.GetString(39), dr.GetInt32(40), dr.GetString(41), dr.GetString(42), dr.GetString(43));
                }
                dr.Close();
            }
            return ci;
        }

        public int deteleOnBulk(PipelineBulkInfo Info)
        {
            int status = 0;
            try
            {
                Database db = DBConnection.CreateDatabase();

                DbCommand cmd = db.GetSqlStringCommand(@"
                                UPDATE TBL_PIPELINE_TMP SET IS_DELETED = '1',STATUS = 'P', UPDATE_BY = @UPDATE_BY, UPDATE_DATE = @UPDATE_DATE, ACT_TYPE = @ACT_TYPE
                                WHERE (LTRIM(RTRIM(UNIT_CODE)) = LTRIM(RTRIM(@UNIT_CODE))) AND (LTRIM(RTRIM(FULL_NAME))=LTRIM(RTRIM(@FULL_NAME)))
                                AND (LTRIM(RTRIM(ADDRESS_BUSINESS)) = LTRIM(RTRIM(@ADDRESS_BUSINESS)));
                            ");
                db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, Info.UNIT_CODE);
                db.AddInParameter(cmd, "@FULL_NAME", DbType.String, Info.FULL_NAME);
                db.AddInParameter(cmd, "@ADDRESS_BUSINESS", DbType.String, Info.ADDRESS_BUSINESS);
                db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, Info.UPDATE_BY);
                db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, Info.UPDATE_DATE);
                db.AddInParameter(cmd, "@ACT_TYPE", DbType.String, Info.ACT_TYPE);
                status = db.ExecuteNonQuery(cmd);
            }
            catch (Exception e) { }
            return status;
        }

        public List<PipelineBulkInfo> GetListPipelineExcel(string value, string batch_id)
        {
            List<PipelineBulkInfo> list = new List<PipelineBulkInfo>();
            Database db = DBConnection.CreateDatabase();
            string sql;
            DbCommand cmd;
            sql = @"SELECT  * FROM TBL_PIPELINE_TMP WHERE [BATCH_ID] = @BATCH_ID 
                    AND IS_DELETED = '0' AND STATUS = 'R' AND ((UNIT_CODE LIKE '%' + @PARAM + '%') OR (FULL_NAME LIKE '%' + @PARAM + '%'))";
            cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@PARAM", DbType.String, value);
            db.AddInParameter(cmd, "@BATCH_ID", DbType.String, batch_id);
            cmd.CommandTimeout = 3600;

            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new PipelineBulkInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetDouble(3), dr.GetString(4), dr.GetString(5), dr.GetString(6), dr.GetString(7), dr.GetString(8), dr.GetString(9), dr.GetString(10), dr.GetString(11), dr.GetDateTime(12), dr.GetString(13), dr.GetString(14), dr.GetString(15), dr.GetDouble(16), dr.GetDouble(17), dr.GetDouble(18), dr.GetDouble(19), dr.GetDouble(20), dr.GetDouble(21), dr.GetDouble(22), dr.GetDouble(23), dr.GetDouble(24), dr.GetDouble(25), dr.GetDouble(26), dr.GetDouble(27), dr.GetDouble(28), dr.GetString(29), dr.GetDateTime(30), dr.GetString(31), dr.GetDateTime(32), dr.GetString(33), dr.GetString(34), dr.GetDateTime(35), dr.GetString(36), dr.GetString(37), dr.GetString(38), dr.GetString(39), dr.GetInt32(40), dr.GetString(41), dr.GetString(42), dr.GetString(43)));
                }
                dr.Close();
            }
            return list;
        }

        public List<PipelineBulkInfo> GetListPipelineeExcelReal(string value, string batch_id)
        {
            List<PipelineBulkInfo> list = new List<PipelineBulkInfo>();
            Database db = DBConnection.CreateDatabase();
            string sql;

            DbCommand cmd;
            sql = "SELECT  * FROM ( " +
                  " SELECT ROW_NUMBER() OVER (ORDER BY PT.[UPDATE_DATE] ASC) AS Row, " +
                  " ISNULL(CAST(P.[ROW_ID] AS VARCHAR(40)), '') AS ROW_ID,PT.[UNIT_CODE],PT.[FULL_NAME],PT.[JENIS_USAHA] " +
                     " ,PT.[RENCANA_PLAFON],PT.[MOBILE_PHONE],PT.[OFFICE_PHONE] " +
                     " ,PT.[RESIDENTIAL_PHONE],PT.[KODYA_KAB_BUSINESS],PT.[KECAMATAN_BUSINESS] " +
                     " ,PT.[KELURAHAN_BUSINESS],PT.[ADDRESS_BUSINESS],PT.[ZIP_CODE_BUSINESS] " +
                     " ,PT.[DATE_VISIT_PLAN],PT.[PROSPECT_TYPE],PT.[DEBTOR_TYPE] " +
                     " ,PT.[CHANNELING],PT.[OMZET_1],PT.[OMZET_2] " +
                     " ,PT.[OMZET_3],PT.[OMZET_4],PT.[OMZET_5] " +
                     " ,PT.[OMZET_6],PT.[OMZET_7],PT.[OMZET_8] " +
                     " ,PT.[OMZET_9],PT.[OMZET_10],PT.[OMZET_11] " +
                     " ,PT.[OMZET_12],PT.[OMZET_AVERAGE],PT.[ENTRY_BY] " +
                     " ,PT.[ENTRY_DATE],PT.[UPDATE_BY],PT.[UPDATE_DATE] " +
                     " ,ISNULL(P.[IS_DOWNLOADED], '') IS_DOWNLOADED, ISNULL(P.[DOWNLOAD_BY], '') DOWNLOAD_BY, ISNULL(P.[DOWNLOAD_DATE], '01-01-2009') DOWNLOAD_DATE " +
                     " ,PT.[EKSTERNAL],PT.[REFERENSI],ISNULL(P.[IS_ASSIGNED], '') IS_ASSIGNED " +
                     " ,ISNULL(P.[ASSIGNED_TO], '') ASSIGNED_TO, ISNULL(P.[ASSIGNED_DATE], '01-01-2009') ASSIGNED_DATE,PT.[BATCH_ID],PT.[FLAG_FREEZE],PT.[ACT_TYPE] " +
                  " FROM [TBL_PIPELINE] P RIGHT OUTER JOIN TBL_PIPELINE_TMP PT " +
                " ON P.UNIT_CODE = PT.UNIT_CODE  " +
                    " AND P.FULL_NAME = PT.FULL_NAME " +
                    " AND P.ADDRESS_BUSINESS = PT.ADDRESS_BUSINESS " +
                " WHERE PT.ERR_LINE = 0 AND PT.STATUS = 'A' AND PT.[BATCH_ID] = @BATCH_ID AND ((PT.UNIT_CODE LIKE '%' + @PARAM + '%') OR (PT.FULL_NAME LIKE '%' + @PARAM + '%')) " +
                  ") AS LogWithRowNumbers";
                  
            cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@PARAM", DbType.String, value);
            db.AddInParameter(cmd, "@BATCH_ID", DbType.String, batch_id);
            cmd.CommandTimeout = 3600;

            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    //list.Add(new PipelineBulkInfo(dr.GetGuid(0).ToString(), dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetDouble(4), dr.GetString(5), dr.GetString(6), dr.GetString(7), dr.GetString(8), dr.GetString(9), dr.GetString(10), dr.GetString(11), dr.GetString(12), dr.GetDateTime(13), dr.GetString(14), dr.GetString(15), dr.GetString(16), dr.GetDouble(17), dr.GetDouble(18), dr.GetDouble(19), dr.GetDouble(20), dr.GetDouble(21), dr.GetDouble(22), dr.GetDouble(23), dr.GetDouble(24), dr.GetDouble(25), dr.GetDouble(26), dr.GetDouble(27), dr.GetDouble(28), dr.GetDouble(29), dr.GetString(30), dr.GetDateTime(31), dr.GetString(32), dr.GetDateTime(33), dr.GetString(34), dr.GetString(35), dr.GetDateTime(36), dr.GetString(37), dr.GetString(38), dr.GetString(39), dr.GetString(40), dr.GetDateTime(41), dr.GetString(42), dr.GetInt16(43), dr.GetString(44)));
                    list.Add(new PipelineBulkInfo(dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetDouble(5), dr.GetString(6), dr.GetString(7), dr.GetString(8), dr.GetString(9), dr.GetString(10), dr.GetString(11), dr.GetString(12), dr.GetString(13), dr.GetDateTime(14), dr.GetString(15), dr.GetString(16), dr.GetString(17), dr.GetDouble(18), dr.GetDouble(19), dr.GetDouble(20), dr.GetDouble(21), dr.GetDouble(22), dr.GetDouble(23), dr.GetDouble(24), dr.GetDouble(25), dr.GetDouble(26), dr.GetDouble(27), dr.GetDouble(28), dr.GetDouble(29), dr.GetDouble(30), dr.GetString(31), dr.GetDateTime(32), dr.GetString(33), dr.GetDateTime(34), dr.GetString(35), dr.GetString(36), dr.GetDateTime(37), dr.GetString(38), dr.GetString(39), dr.GetString(40), dr.GetString(41), dr.GetDateTime(42), dr.GetString(43), dr.GetInt16(44), dr.GetString(45)));
                }
                dr.Close();
            }
            return list;
        }
    }
}
